package greenplum;
/*
 * Date: 13-12-26
 */

import gudusoft.gsqlparser.EDataType;
import gudusoft.gsqlparser.EDbVendor;
import gudusoft.gsqlparser.TGSqlParser;
import gudusoft.gsqlparser.nodes.TParameterDeclaration;
import gudusoft.gsqlparser.stmt.*;
import junit.framework.TestCase;

public class testCreateFunction extends TestCase {

    public void test1(){
        TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvgreenplum);
        sqlparser.sqltext = "CREATE FUNCTION complex_add(complex, complex)\n" +
                "RETURNS complex\n" +
                "AS 'filename', 'complex_add'\n" +
                "LANGUAGE C IMMUTABLE STRICT;";
       // System.out.println(sqlparser.sqltext);
        assertTrue(sqlparser.parse() == 0);

        TCreateFunctionStmt createFunction = (TCreateFunctionStmt)sqlparser.sqlstatements.get(0);
        assertTrue(createFunction.getFunctionName().toString().equalsIgnoreCase("complex_add"));
        assertTrue(createFunction.getParameterDeclarations().size() == 2);
        TParameterDeclaration parameterDeclaration = (TParameterDeclaration)createFunction.getParameterDeclarations().getParameterDeclarationItem(0);
        assertTrue(parameterDeclaration.getDataType().getDataType() == EDataType.generic_t);
        assertTrue(createFunction.getReturnDataType().getDataType() == EDataType.generic_t);
        assertTrue(createFunction.getProcedureLanguage().toString().equalsIgnoreCase("C"));
        assertTrue(createFunction.getObjfile().toString().equalsIgnoreCase("'filename'"));
        assertTrue(createFunction.getLinkSymbol().toString().equalsIgnoreCase("'complex_add'"));
    }


    public void testplpgsql(){
        TGSqlParser sqlparser = new TGSqlParser(EDbVendor.dbvgreenplum);
        sqlparser.sqltext = "--==============================================================================\n" +
                "\n" +
                "-- DBMS Name      :    Greenplum\n" +
                "\n" +
                "-- Script Name    :    update_wrk_bdw_gz_account_rollup_AFS_facility\n" +
                "\n" +
                "-- Description    :    Update the Stage  table wrk_bdw_gz_account_rollup_AFS_facility\n" +
                "\n" +
                "-- Generated by   :    WhereScape RED Version 6.8.3.4 (build 150604-150128 RC)\n" +
                "\n" +
                "-- Generated for  :    Union Bank\n" +
                "\n" +
                "-- Generated on   :    Tuesday, March 15, 2016 at 13:17:50\n" +
                "\n" +
                "-- Author         :    xu82760\n" +
                "\n" +
                "--==============================================================================\n" +
                "\n" +
                "-- Notes / History\n" +
                "\n" +
                "--\n" +
                "\n" +
                "\n" +
                "\n" +
                "CREATE OR REPLACE FUNCTION FUNCTIONOWNER.update_wrk_bdw_gz_account_rollup_AFS_facility\n" +
                "\n" +
                "  (\n" +
                "\n" +
                "  p_sequence      IN    integer,\n" +
                "\n" +
                "  p_job_name      IN    varchar(256),\n" +
                "\n" +
                "  p_task_name     IN    varchar(256),\n" +
                "\n" +
                "  p_job_id        IN    integer,\n" +
                "\n" +
                "  p_task_id       IN    integer,\n" +
                "\n" +
                "  p_parameters    INOUT varchar[][],\n" +
                "\n" +
                "  p_return_msg    INOUT varchar(256),\n" +
                "\n" +
                "  p_status        INOUT integer\n" +
                "\n" +
                "  )\n" +
                "\n" +
                "RETURNS RECORD\n" +
                "\n" +
                "AS $BODY$\n" +
                "\n" +
                "DECLARE\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  -- Control variables used in most programs\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  v_msgtext           varchar(255);  -- Text for audit_trail\n" +
                "\n" +
                "  v_sql               varchar(255);  -- Text for SQL statements\n" +
                "\n" +
                "  v_set               integer;       -- commit set\n" +
                "\n" +
                "  v_analyze_flag      integer;       -- analyze flag\n" +
                "\n" +
                "  v_step              integer;       -- return code\n" +
                "\n" +
                "  v_insert_count      integer;       -- no of records inserted\n" +
                "\n" +
                "  v_count             integer;       -- General counter\n" +
                "\n" +
                "  v_sql_code          integer;       -- SQL Error Code for Audit Trail\n" +
                "\n" +
                "  v_sql_error         varchar(255);  -- SQL Error Code for Audit Trail as varchar\n" +
                "\n" +
                "  v_msg_type          varchar(10);   -- Message Type for WsWrkError Calls\n" +
                "\n" +
                "\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  -- General Variables\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  v_return_status     integer;       -- Update result status\n" +
                "\n" +
                "  v_row_count         integer;       -- General row count\n" +
                "\n" +
                "  v_status            integer;       -- General status field\n" +
                "\n" +
                "  v_current_timestamp timestamp;     -- Used for create/update dates\n" +
                "\n" +
                "  v_current_datetime  date     ;     -- Used for create/update dates\n" +
                "\n" +
                "  v_current_date      date;          -- Current Date used for start and end dates\n" +
                "\n" +
                "\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  -- Main\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  BEGIN\n" +
                "\n" +
                "  v_step := 100;\n" +
                "\n" +
                "  v_row_count         := 0;\n" +
                "\n" +
                "  v_insert_count      := 0;\n" +
                "\n" +
                "  v_current_timestamp := CURRENT_TIMESTAMP;\n" +
                "\n" +
                "  v_current_datetime  := NOW();\n" +
                "\n" +
                "  v_current_date      := CURRENT_DATE;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  -- Delete any records that we will be replacing\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  TRUNCATE TABLEOWNER.wrk_bdw_gz_account_rollup_AFS_facility;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  v_step := 200;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  -- Insert new records\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  BEGIN\n" +
                "\n" +
                "  v_row_count := 0;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  INSERT INTO TABLEOWNER.wrk_bdw_gz_account_rollup_AFS_facility\n" +
                "\n" +
                "  ( account_number\n" +
                "\n" +
                "  , as_of_date\n" +
                "\n" +
                "  , source_system_code\n" +
                "\n" +
                "  , committed_ult_parent_account_number\n" +
                "\n" +
                "  , cif_number\n" +
                "\n" +
                "  , source_system_customer_number\n" +
                "\n" +
                "  , booking_office_responsible_center\n" +
                "\n" +
                "  , account_opening_date\n" +
                "\n" +
                "  , account_type_code\n" +
                "\n" +
                "  , account_schedule_code\n" +
                "\n" +
                "  , commitment_type_code\n" +
                "\n" +
                "  , open_closed_ind\n" +
                "\n" +
                "  , closed_date\n" +
                "\n" +
                "  , closed_in_prev_period_indicator\n" +
                "\n" +
                "  , commitment_amount\n" +
                "\n" +
                "  , commitment_alloc_amount\n" +
                "\n" +
                "  , commitment_unalloc_amount\n" +
                "\n" +
                "  , part_commitment_amount\n" +
                "\n" +
                "  , outstanding_balance\n" +
                "\n" +
                "  , part_out_balance\n" +
                "\n" +
                "  , run_identifier\n" +
                "\n" +
                "  , population_timestamp\n" +
                "\n" +
                "  )\n" +
                "\n" +
                "  SELECT\n" +
                "\n" +
                "         afsoblig_foc03_data.foc03_fc_oblg_mc010 || afsoblig_foc03_data.foc03_fc_obln_mc015\n" +
                "\n" +
                "       , afsoblig_foc03_data.period_dt\n" +
                "\n" +
                "       , 'AFS'\n" +
                "\n" +
                "       , afsoblig_foc03_data.foc03_fc_oblg_mc010 || afsoblig_foc03_data.foc03_fc_obln_mc015\n" +
                "\n" +
                "       , CAST(gz_customer_xref.btmu_cif_number AS varchar(8))\n" +
                "\n" +
                "       , afsoblig_foc03_data.foc03_fc_oblg_mc010\n" +
                "\n" +
                "       , afsoblig_foc03_data.foc03_fc_serv_unit_mc080\n" +
                "\n" +
                "       , afsoblig_foc03_data.foc03_fc_eff_date_mc044::date\n" +
                "\n" +
                "       , 'CF'\n" +
                "\n" +
                "       , string_to_array('03a',',')\n" +
                "\n" +
                "       , 'C'\n" +
                "\n" +
                "       , Case\n" +
                "\n" +
                "\twhen lpad(afsoblig_foc03_data.foc03_fc_assgn_unit_mc081,5,'0')\n" +
                "\n" +
                "\t\tin ('17808', '17908','84300','96405','99042') then 'N'\n" +
                "\n" +
                "\twhen lpad(afsoblig_foc03_data.foc03_fc_assgn_unit_mc081,5,'0')\n" +
                "\n" +
                "\t\tnot in ('17808', '17908','84300','96405','99042') and afsoblig_foc03_data.foc03_fc_curr_prin_mc061 = 0.00 and afsoblig_foc03_data.foc03_fc_prin_bld_npd_mc151 = 0.00 then 'N'\n" +
                "\n" +
                "\twhen lpad(afsoblig_foc03_data.foc03_fc_assgn_unit_mc081,5,'0')\n" +
                "\n" +
                "\t\tnot in ('17808', '17908','84300','96405','99042') and (afsoblig_foc03_data.foc03_fc_curr_prin_mc061 <> 0.00 or afsoblig_foc03_data.foc03_fc_prin_bld_npd_mc151 <> 0.00) then 'Y'\n" +
                "\n" +
                "\telse ''\n" +
                "\n" +
                "end\n" +
                "\n" +
                "       , afsoblig_foc03_data.foc03_fc_legal_mat_dt_mc048::date\n" +
                "\n" +
                "       , CASE\n" +
                "\n" +
                "WHEN\n" +
                "\n" +
                "(\n" +
                "\n" +
                "CASE\n" +
                "\n" +
                "  WHEN afsoblig_foc03_data.foc03_f_commt_bal_mc066 = 0.00\n" +
                "\n" +
                "  AND afsoblig_foc03_data.foc03_fc_dt_out_dbt_mc050 = afsoblig_foc03_data.foc03_fc_dt_lst_activ\n" +
                "\n" +
                "    AND   afsoblig_foc03_data.foc03_fc_close_ind_mc574>'0'\n" +
                "\n" +
                "  THEN 'N'\n" +
                "\n" +
                "     ELSE 'Y'\n" +
                "\n" +
                "END\n" +
                "\n" +
                ") = 'Y'\n" +
                "\n" +
                "THEN 'N'\n" +
                "\n" +
                "WHEN\n" +
                "\n" +
                "(\n" +
                "\n" +
                "CASE\n" +
                "\n" +
                "  WHEN afsoblig_foc03_data.foc03_f_commt_bal_mc066 = 0.00\n" +
                "\n" +
                "  AND afsoblig_foc03_data.foc03_fc_dt_out_dbt_mc050 = afsoblig_foc03_data.foc03_fc_dt_lst_activ\n" +
                "\n" +
                "    AND   afsoblig_foc03_data.foc03_fc_close_ind_mc574> '0'\n" +
                "\n" +
                "  THEN 'N'\n" +
                "\n" +
                "     ELSE 'Y'\n" +
                "\n" +
                "END\n" +
                "\n" +
                ") = 'N'\n" +
                "\n" +
                "THEN CASE\n" +
                "\n" +
                "WHEN (\n" +
                "\n" +
                "date_part('year', afsoblig_foc03_data.foc03_fc_legal_mat_dt_mc048) = date_part('year', afsoblig_foc03_data.period_dt)\n" +
                "\n" +
                "AND date_part('month', afsoblig_foc03_data.foc03_fc_legal_mat_dt_mc048) < date_part('month', afsoblig_foc03_data.period_dt)\n" +
                "\n" +
                ")\n" +
                "\n" +
                "OR date_part('year', afsoblig_foc03_data.foc03_fc_legal_mat_dt_mc048) < date_part('year', afsoblig_foc03_data.period_dt)\n" +
                "\n" +
                "THEN 'Y'\n" +
                "\n" +
                "ELSE 'N'\n" +
                "\n" +
                "END\n" +
                "\n" +
                "END\n" +
                "\n" +
                "       , afsoblig_foc03_data.foc03_f_commt_bal_mc066::numeric\n" +
                "\n" +
                "       , afsoblig_foc03_data.foc03_f_prin_amt_takendown::numeric\n" +
                "\n" +
                "       , CAST(afsoblig_foc03_data.FOC03_F_COMMT_BAL_MC066::numeric - afsoblig_foc03_data.foc03_f_prin_amt_takendown::numeric\n" +
                "\n" +
                "     AS NUMERIC)\n" +
                "\n" +
                "       , CAST(afsoblig_foc03_data.foc03_f_prin_amt_takendown::numeric * afsoblig_foc03_data.foc03_FC_TOT_PART_PCT_MC736::numeric\n" +
                "\n" +
                "    AS numeric)\n" +
                "\n" +
                "       , CAST (CASE WHEN afsoblig_foc03_data.foc03_f_prin_amt_takendown::numeric >= 0\n" +
                "\n" +
                "     THEN afsoblig_foc03_data.foc03_f_prin_amt_takendown::numeric\n" +
                "\n" +
                "     ELSE afsoblig_foc03_data.foc03_f_commt_bal_mc066::numeric + afsoblig_foc03_data.foc03_f_prin_amt_takendown::numeric\n" +
                "\n" +
                "END AS numeric)\n" +
                "\n" +
                "       , afsoblig_foc03_data.FOC03_FC_T_PART_AMT_MC732::numeric\n" +
                "\n" +
                "       , p_sequence\n" +
                "\n" +
                "       , current_timestamp\n" +
                "\n" +
                "  FROM\n" +
                "\n" +
                "  (\n" +
                "\n" +
                "  SELECT\n" +
                "\n" +
                "  a.period_dt\n" +
                "\n" +
                "  ,a.foc03_fc_oblg_mc010\n" +
                "\n" +
                "  ,a.foc03_fc_obln_mc015\n" +
                "\n" +
                "  ,a.foc03_fc_proc_type_mc038\n" +
                "\n" +
                "  ,a.foc03_fc_gl_code_mc083\n" +
                "\n" +
                "  ,a.foc03_fc_type_mc043\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_curr_prin_mc061, a.foc03_fc_curr_prin_mc061) foc03_fc_curr_prin_mc061\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_assgn_unit_mc081, a.foc03_fc_assgn_unit_mc081) foc03_fc_assgn_unit_mc081\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_prin_bld_npd_mc151, a.foc03_fc_prin_bld_npd_mc151) foc03_fc_prin_bld_npd_mc151\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_payoff_rsn_cd_mc618, a.foc03_fc_payoff_rsn_cd_mc618) foc03_fc_payoff_rsn_cd_mc618\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_legal_mat_dt_mc048, a.foc03_fc_legal_mat_dt_mc048) foc03_fc_legal_mat_dt_mc048\n" +
                "\n" +
                "  ,coalesce(b.foc03_f_commt_bal_mc066, a.foc03_f_commt_bal_mc066) foc03_f_commt_bal_mc066\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_serv_unit_mc080, a.foc03_fc_serv_unit_mc080) foc03_fc_serv_unit_mc080\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_eff_date_mc044, a.foc03_fc_eff_date_mc044) foc03_fc_eff_date_mc044\n" +
                "\n" +
                "  ,coalesce(b.FOC03_FC_EST_MAT_DT_MC049, a.FOC03_FC_EST_MAT_DT_MC049) FOC03_FC_EST_MAT_DT_MC049\n" +
                "\n" +
                "  ,coalesce(b.foc03_f_prin_amt_takendown, a.foc03_f_prin_amt_takendown) foc03_f_prin_amt_takendown\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_tot_part_pct_mc736, a.foc03_fc_tot_part_pct_mc736) foc03_fc_tot_part_pct_mc736\n" +
                "\n" +
                "  ,coalesce(b.FOC03_FC_BK_ASSET_BAL_MC904, a.FOC03_FC_BK_ASSET_BAL_MC904) FOC03_FC_BK_ASSET_BAL_MC904\n" +
                "\n" +
                "  ,coalesce(b.FOC03_FC_T_PART_AMT_MC732, a.FOC03_FC_T_PART_AMT_MC732) FOC03_FC_T_PART_AMT_MC732\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_dt_out_dbt_mc050, a.foc03_fc_dt_out_dbt_mc050) foc03_fc_dt_out_dbt_mc050\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_dt_lst_activ, a.foc03_fc_dt_lst_activ) foc03_fc_dt_lst_activ\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_close_ind_mc574, a.foc03_fc_close_ind_mc574) foc03_fc_close_ind_mc574\n" +
                "\n" +
                "  ,coalesce(b.foc03_fc_intern_comm_mc039, a.foc03_fc_intern_comm_mc039) foc03_fc_intern_comm_mc039\n" +
                "\n" +
                "  FROM TABLEOWNER.afsoblig_foc03_data a\n" +
                "\n" +
                "  LEFT JOIN TABLEOWNER.afsoblig_foc03_data_swp b\n" +
                "\n" +
                "  ON a.foc03_fc_oblg_mc010 = b.foc03_fc_oblg_mc010\n" +
                "\n" +
                "  AND a.foc03_fc_obln_mc015 = b.foc03_fc_obln_mc015\n" +
                "\n" +
                "  AND b.period_dt = udw_ops_cmn.get_udw_cntrl_param_value ('BDW','BDW_PROCESS_DATE')::date\n" +
                "\n" +
                "  WHERE a.period_Dt = udw_ops_cmn.get_udw_cntrl_param_value ('BDW','BDW_PROCESS_DATE')::date\n" +
                "\n" +
                "  ) afsoblig_foc03_data\n" +
                "\n" +
                "  LEFT OUTER JOIN udw_gz_extract_pres.gz_customer_xref gz_customer_xref\n" +
                "\n" +
                "  ON afsoblig_foc03_data.foc03_fc_oblg_mc010 = substring(gz_customer_xref.account_number from 9 for 10)\n" +
                "\n" +
                "  AND gz_customer_xref.source_system='AFS'\n" +
                "\n" +
                "  AND gz_customer_xref.primary_customer_indicator ='Y'\n" +
                "\n" +
                "  AND afsoblig_foc03_data.period_dt=gz_customer_xref.as_of_date\n" +
                "\n" +
                "  WHERE afsoblig_foc03_data.period_dt = udw_ops_cmn.get_udw_cntrl_param_value ('BDW','BDW_PROCESS_DATE')::date\n" +
                "\n" +
                "  AND Substring(afsoblig_foc03_data.foc03_fc_proc_type_mc038, 1, 1) in ('0','1')\n" +
                "\n" +
                "  AND Substring(afsoblig_foc03_data.foc03_fc_proc_type_mc038, 3, 2) <> ('02')\n" +
                "\n" +
                "  ;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  GET DIAGNOSTICS v_row_count = row_count;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  END;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  v_insert_count := v_insert_count + v_row_count;\n" +
                "\n" +
                "\n" +
                "\n" +
                "\n" +
                "\n" +
                "  v_step := 300;\n" +
                "\n" +
                "\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "  -- Final settings\n" +
                "\n" +
                "  --============================================================================\n" +
                "\n" +
                "\n" +
                "\n" +
                "  -- Work out the return message\n" +
                "\n" +
                "\n" +
                "\n" +
                "  p_status := 1;\n" +
                "\n" +
                "  p_return_msg := 'wrk_bdw_gz_account_rollup_AFS_facility truncated and updated. '\n" +
                "\n" +
                "    || CAST(v_insert_count AS VARCHAR(64)) || ' records added. ';\n" +
                "\n" +
                "RETURN;\n" +
                "\n" +
                "\n" +
                "\n" +
                "EXCEPTION\n" +
                "\n" +
                "WHEN OTHERS THEN\n" +
                "\n" +
                "  v_sql_code := 0;\n" +
                "\n" +
                "  v_sql_error := SQLERRM;\n" +
                "\n" +
                "  p_return_msg := 'Unhandled Exception in update_wrk_bdw_gz_account_rollup_AFS_facility. '||\n" +
                "\n" +
                "    ' Step ' ||v_step;\n" +
                "\n" +
                "  RAISE NOTICE 'WsWrkAudit:F|%|%|%|%|%|%|%|%|',p_job_name, p_task_name, p_sequence,\n" +
                "\n" +
                "    p_return_msg,v_sql_code,v_sql_error,p_task_id, p_job_id;\n" +
                "\n" +
                "  p_status := -3;\n" +
                "\n" +
                "\n" +
                "\n" +
                "RETURN;\n" +
                "\n" +
                "\n" +
                "\n" +
                "END;\n" +
                "\n" +
                "$BODY$ LANGUAGE plpgsql;\n" +
                "\n" +
                "\n";
       //
        assertTrue(sqlparser.parse() == 0);

        TCreateFunctionStmt createFunction = (TCreateFunctionStmt)sqlparser.sqlstatements.get(0);
        assertTrue(createFunction.getFunctionName().toString().equalsIgnoreCase("FUNCTIONOWNER.update_wrk_bdw_gz_account_rollup_AFS_facility"));
        assertTrue(createFunction.getProcedureLanguage().toString().equalsIgnoreCase("plpgsql"));
        assertTrue(createFunction.getBodyStatements().size()==14);
        //System.out.println(createFunction.getBodyStatements().size());
    }
}
